Skip Headers

Oracle® Enterprise Manager Advanced Configuration 10g Release 2 (10.2) Part Number B16242-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 13 User-Defined Metrics

User-Defined Metrics allow you to extend the reach of Enterprise Manager's monitoring to conditions specific to particular environments via custom scripts or SQL queries and function calls. Once defined, User-Defined Metrics will be monitored, aggregated in the repository and trigger alerts like regular metrics.

This chapter covers the following topics:

- Extending Monitoring Capability

- Creating OS-Based User-Defined Metrics

- Creating a SQL-Based User-Defined Metric

- Notifications, Corrective Actions, and Monitoring Templates

## 13.1 Extending Monitoring Capability

There are two types of User-Defined Metrics:

- OS-Based User-Defined Metrics: Accessed from Host target home pages, these User-Defined Metrics allow you to define new metrics using custom Operating System (OS) scripts.

  To monitor for a particular condition (for example, check successful completion of monthly system maintenance routines), you can write a custom script that will monitor that condition, then create an OS-based User-Defined Metric that will use your custom script. Each time the metric is evaluated by Enterprise Manager, it will use the specified script, relying on that script to return the value of the condition.

- SQL-Based User-Defined Metrics: Accessed from Database target home pages, these User-Defined Metrics allow you to implement custom database monitoring using SQL queries or function calls.

  SQL-based User-Defined Metrics do not use external scripts. You enter SQL directly into the Enterprise Manager user interface at the time of metric creation

Once a User-Defined Metric is created, all other monitoring features, such as alerts, notifications, historical collections, and corrective actions are automatically available to it.

Administrators who already have their own library of custom monitoring scripts can leverage these monitoring features by integrating their scripts with Enterprise Manager via User-Defined Metrics. Likewise, existing SQL queries or function calls currently used to monitor database conditions can be easily integrated into Enterprise Manager's monitoring framework using the SQL-based User-Defined Metric.

## 13.2 Creating OS-Based User-Defined Metrics

Creating an OS-based User-Defined Metric involves two steps:

- Step 1: Create Your OS Monitoring Script

- Step 2: Register the Script as a User-Defined Metric

### 13.2.1 Create Your OS Monitoring Script

Using a scripting language of your choice, create a script that contains logic to check for the condition being monitored. For example, scripts that check for disk space or memory usage. All scripts to be run with User-Defined Metrics should be placed in a directory to which the Management Agent has full access privileges. Scripts themselves must have the requisite permissions set so that they can be executed by the Management Agent. The script runtime environment must also be configured: If your script requires an interpreter, such as a Perl interpreter, this must be installed on that host as well.

All monitoring scripts should contain code to perform the following basic functions:

- Code to check the status of monitored objects

- Code to return script results to Enterprise Manager

#### 13.2.1.1 Code to check the status of monitored objects

Define logic in the code that checks the condition being monitored such as determining the amount of free space on a particular file system or level of memory usage.

After checking the monitored condition, the script should return the value associated with the monitored object.

When you choose to have the script return a specific value from the monitored object (for example, current disk space usage), you can also have Enterprise Manager evaluate the object's current value against specific warning and critical thresholds. You specify these warning and critical thresholds from the Grid Control console at the time you create the User-Defined Metric. Based on the evaluation of the metric's value against the thresholds, an alert may be triggered at one of the following severity levels:

Table 13-1 Metric Severity Levels

Severity Level | Status
--- | ---
Script Failure | The script failed to run properly.
Clear | No problems with the object monitored; status is clear. If thresholds were specified for the metric, then it means the thresholds were not reached.
Warning | The value of the monitored object reached the warning threshold.
Critical | The value of the monitored object reached the critical threshold.

#### 13.2.1.2 Code to return script results to Enterprise Manager

After checking the monitored condition, the script should return the value associated with the monitored object. The script returns values back to Enterprise Manager by sending formatted information to standard output (stdout) using the syntax that is consistent with the scripting language (the "print" statement in Perl, for example). Enterprise Manager then checks the standard output of a script for this formatted information; specifically it checks for the tags: em_result and em_message and the values assigned to these tags.

The script must assign the value of the monitored object to the tag em_result. The output must be written as a string delimited by new line characters. For example, if the value of the monitored object is 200, your script can return this to Enterprise Manager as shown in this Perl statement:

    print "em_result=200\n"

You can also have Enterprise Manager evaluate the returned value against specified warning and critical thresholds. You specify these warning and critical thresholds when you register your script as a User-Defined Metric in the console.

If the comparison between the warning or critical threshold holds true, a warning or critical alert will be generated. The default message for this alert will be:

    "The value is $em_result".

You can choose to override this default message with a custom message by assigning the string to be used to the tag em_message.

For example, if you want your alert message to say 'Disk usage is high", your script can return this custom message as follows:

    print "em_message=Disk usage is high\n"

Important: Script output tags must be lower-case in order for Enterprise Manager to recognize the script output as valid User-Defined Metric feedback. Messages or values associated with each tag can be mixed case. Valid tag output: `em_result=My Value\n` Invalid tag output: `Em_Result=My Value\n`

For a successful script execution, the script output must start with the "em_result=" string in a new line. The message must start with the "em_message=" string in a new line.

The following table summarizes the script output tags.

Table 13-2 Script Output Information Tags

Tag | Definition
--- | ---
em_result | Use this tag to return script result values. Exactly one em_result tag must be found in STDOUT. If more than one em_result tag is found, the first tag encountered will be used; subsequent em_result tags will be ignored. Example:
    print "em_result=200\n"
Returns 200 as the value of the monitored object.
em_message | Use this tag to specify a message with the script result value in STDOUT. For OS-based User-Defined Metrics, only one em_message tag is permitted. If you submit more than one em_message tag, only the first tag is used. Subsequent tags are ignored. Example:
    print "em_result=200\nem_message=Disk usage is high\n"
Returns 200 as the value of the monitored object in addition to the message "Disk usage is high". If you want to include the value of em_result in the message, you can use the placeholder $em_result. Example:
    print "em_message=Disk usage is at $em_result.\n"
If script execution is successful AND it does not contain a em_message string, a default em_message string is automatically generated. The following message format is used:
    em_message=The value is $em_result
Example:
    print "em_result=200\n"
Returns 200 as the value of the monitored object and the generated message "The value is 200"

The output of the user-defined monitoring script must be either em_result or em_message. In the event of system error, such as Perl aborting and writing information to STDERR pertaining to invalid commands, the script returns:

- Non-zero value

- STDOUT and STDERR messages are concatenated and sent to STDERR

This error situation results in a metric error for this User-Defined Metric. You can view metric errors in the Errors page of the Alerts tab in the Enterprise Manager console.

OS Script Location

Oracle recommends that User-Defined Metric OS scripts reside in a location outside the Agent Oracle Home. Doing so isolates scripts from any changes that may occur as a result of an Agent upgrade and ensures your scripts remain operational. When registering your script in the Grid Control console, you must specify the full path to the script. Do not use Available Properties (for example, %scriptsDir% or %emdRoot%) as part of the path specification.

#### 13.2.1.3 Script Runtime Environment

When the User-Defined Metric is evaluated, it executes the script using the credentials (user name and password) specified at the time the User-Defined Metric was registered in the Enterprise Manager Console. See "Register the Script as a User-Defined Metric". Ensure that the user name and password you specify for the User-Defined Metric is an active account (on that machine) possessing the requisite permissions to run the script.

### 13.2.2 Register the Script as a User-Defined Metric

Once you have created the monitoring script, you are ready to add this monitoring functionality to Enterprise Manager as a User-Defined Metric.

Important: : For OS-based User-Defined Metrics, make sure the Management Agent is up and running on the machine where the monitoring script resides before creating the User-Defined Metric. Operator privilege or higher is required on the host target.

Creating an OS-Based User-Defined Metric

1. From the home page of the Host that has your OS monitoring script (Related Links), choose User-Defined Metrics. The User-Defined Metrics summary page appears containing a list of previously defined User-Defined Metrics. From this page, you perform edit, view, delete, or create like functions on existing User-Defined Metrics.

2. Click Create. The Create User-Defined Metric page appears.

3. Enter the requisite metric definition, threshold, and scheduling information. For the Command Line field, enter the full path to your script, including any requisite shell or interpreters. For example, /bin/sh myscript. See the following section for more details.

4. Click OK. The User-Defined Metric summary page appears with the new User-Defined Metric appended to the list.

If the User-Defined Metric has been created and the severity has not been updated recently, it is possible that there are metric errors associated with the User-Defined Metric execution. In this situation, access the Errors subtab under Alerts tab to check.

Create User-Defined Metric Page (OS-based User-Defined Metric)

The Create User-Defined Metric page allows you to specify the metric information required to successfully register the metric with Enterprise Manager. The page is divided into functional categories:

- Definition: You define the operational and environmental parameters required to run your script, in addition to the name of the script itself.

- Operating System Credentials: You enter the credentials used to run the monitoring script. See Enterprise Manager online help for more details on Response Actions. This functional area appears when creating OS-based User-Defined Metrics.

- Thresholds: To have the value returned by your script compared with set threshold values, enter the requisite threshold information. The value of the monitored metric returned by your script (as specified by em_result) will be compared against the thresholds you specify. If the comparison holds true, a warning or critical alert may be generated.

- Schedule: Specify the start time and frequency at which the user-defined script should be run. The time zone used is that of the Agent running on the monitored host.

The following figures show the Create User-Defined Metric pages for an OS-based User-Defined Metric. When accessing this page from any Host home page, the Create User-Defined Metric page appears as shown in Figure 13-1.

Figure 13-1 Create User-Defined Metric Page (OS-Based)

Description of "Figure 13-1 Create User-Defined Metric Page (OS-Based)"

Key elements of this page are described in the following tables.

Table 13-3 Create User-Defined Metric Page: Definition

User-Interface Element | Description
--- | ---
Metric Name | Metric name identifying the user-defined metric in the Enterprise Manager user interface. This name must be unique for all User-Defined Metrics created on that host.
Metric Type | Type of the value returned by the user-defined script. Choose "NUMBER" if your script returns a number. Choose "STRING" if your script returns an alphanumeric text string.
Command Line | Enter the complete command line entry required to execute the user-defined script. You must enter the full command path as well as full path to the script location. For example, to run a Perl script, you might enter something like the following in the Command Line entry field:
    /u1/bin/perl /u1/scripts/myScript.pl
The content of the Command Line is passed as a literal string, so you may use any syntax, special characters, or parameters allowed by your operating system.
Environment | Optional. Enter any environmental variable(s) required to run the user-defined script. A list of predefined properties that can be passed to your script as variables is listed in the Available Properties box. You may also specify your own environment variables. Multiple variables can be defined as a space-separated list. Example: If your script uses three variables (var1, var2, var3) where var1 is the location of the Perl directory (predefined), var2 is the directory where your Perl scripts are stored (predefined), and var3 is an Oracle home, your entry in the Environment text entry field would appear as follows:
    var1=%perlBin% var2=%scriptsDir% var3=/u1/orahome10

Table 13-4 Create User-Defined Metric Page: Operating System

User-Interface Element | Description
--- | ---
User Name | Enter the user name for a valid operating system account on the machine where the script is to be run. Make sure the specified account has the requisite privileges to access the script directory and execute the script.
Password | Enter the password associated with the User Name.

Table 13-5 Create User-Defined Metric Page: Threshold

User-Interface Element | Description
--- | ---
Comparison Operator | Select the comparison method Enterprise Manager should use to compare the value returned by the user-defined script to the threshold values. Available Comparison Operators Operator Value Metric Type Description = Number equal to > Number greater than < Number less than >= Number greater than or equal to <= Number less than or equal to != Number not equal to CONTAINS String contains at least MATCH String exact match
Warning | The value returned by the script is compared to the Warning threshold value using the specified comparison operator. If this comparison holds true, an alert triggers at the warning severity level. Specifically, an alert triggers at the warning severity level if the following comparison is true: <script_value> <comparison_operator> <warning_threshold> and if the consecutive occurrences preceding notification has been reached.
Critical | The value returned by the script is compared to the Critical threshold value using the specified comparison operator. If this comparison holds true, an alert triggers at the critical severity level. Specifically, an alert triggers at the critical severity level if the following comparison is true: <script_value> <comparison_operator> <critical_threshold> and if the consecutive occurrences preceding notification has been reached.
Consecutive Occurrences Preceding Notification | Consecutive number of times a returned value reaches either the warning or critical thresholds before an alert triggers at a warning or critical severity. This feature is useful when monitoring for sustained conditions. For example, if your script monitors the CPU load for a particular machine, you do not want to be notified at every CPU load spike. Instead, you are only concerned if the CPU load remains at a particular threshold (warning or critical) level for a consecutive number of monitoring cycles.
Response Action | Optional. Specify a script or command that will be executed if the User-Defined Metric generates a warning or critical alert. The script or command will be executed using the credentials of the Agent owner. Important: Only an Enterprise Manager Super Administrator can create/edit response actions for metrics. For example, the Management Agent executes the response action if: The Alert severity is Warning or Critical AND There is a change in severity (for example, warning -> critical, critical --> warning, clear --> warning or critical) For more information, see Enterprise Manager online help.

The User-Defined Metric Schedule interface lets you specify when the Management Agent should start monitoring and the frequency at which it should monitor the condition using your OS script.

### 13.2.3 OS-Based User-Defined Metric Example

The sample Perl script used in this example monitors the 5-minute load average on the system. The script performs this function by using the 'uptime' command to obtain the average number of jobs in the run queue over the last 5 minutes.

The script is written in Perl and assumes you have Perl interpreter located in /usr/local/bin on the monitored target.

This script, called `udmload.pl`, is installed in a common administrative script directory defined by the user. For example, /u1/scripts.

Important: Do not store User-Defined Metric monitoring scripts in the same location as Enterprise Manager system scripts.

Full text of the script:

    #!/usr/local/bin/perl

    # Description: 5-min load average.
    # Sample User Defined Event monitoring script.

    $ENV{PATH} = "/bin:/usr/bin:/usr/sbin";

    $DATA = `uptime`;
    $DATA =~ /average:\s+([\.\d]+),\s+([\.\d]+),\s+([\.\d]+)\s*$/;

    if (defined $2) {
       print "em_result=$2\n";
    } else {
       die "Error collecting data\n";
    }

1. Copy the script (udmload.pl) to the monitored target. For example: /u1/scripts. Make sure you have an Enterprise Manager 10g Management Agent running on this machine.

2. Edit the script, if necessary, to point to the location of the Perl interpreter on the monitored target. By default, the script assumes the Perl interpreter is in /usr/local/bin.

3. As a test, run the script: udmload.pl You may need to set its file permissions so that it runs successfully. You should see output of this form:

       em_result=2.1

4. In Create User-Defined Metric page, create a new User-Defined Metric as follows:

   1. Definition Settings

      - Metric Name: Test User-Defined Metric

      - Metric Type: Number

      - Command Line: %perlBin%/perl /u1/scripts/udmload.pl

      - Environment: leave blank

      - Operating System User Name: <OS user able to execute the script>

      - Password: ******

   2. Threshold Settings

      - Comparison Operator: >=

      - Critical Threshold: 0.005

      - Warning Threshold: 0.001

      - Consecutive Occurrences Preceding Notification: 1

      In this example, we want the metric to trigger an alert at a Warning level if the 5-minute load average on the machine reaches 0.001, and trigger an alert at a Critical level if the 5-minute load average reaches 0.005. Feel free to change these thresholds depending on your system.

   3. Schedule Settings:

      - Start: Immediately after creation

      - Frequency: Repeat every 5 minutes. You must specify at least a 5 minute interval.

Setting Up the Sample Script as a User-Defined Metric

When the 5-minute load reaches at least 0.001, you should see the metric trigger an alert.

## 13.3 Creating a SQL-Based User-Defined Metric

You can also define new metrics using custom SQL queries or function calls supported against single instance databases and instances on Real Application Clusters (RAC). To create this type of User-Defined Metric, you must have Enterprise Manager Operator privileges on the database:

1. From the Related Links area of any Database home page, choose User-Defined Metrics. The User-Defined Metrics summary page appears containing a list of previously defined User-Defined Metrics. From this page, you perform edit, view, delete, or create like functions on existing User-Defined Metrics.

2. Click Create. The Create User-Defined Metric page appears.

3. Enter the requisite metric definition, threshold, and scheduling information. For the SQL Query field, enter the query or function call. See the following section for more information.

   Click Test to verify that the SQL query or function call can be executed successfully using the credentials you have specified

4. Click OK. The User-Defined Metric summary page appears with the new User-Defined Metric appended to the list.

If the User-Defined Metric has been created and the severity has not been updated recently, it is possible that there are metric errors associated with the User-Defined Metric execution. In this situation, access the Errors subtab under Alerts tab to check.

Create User-Defined Metric Page (SQL-Based User-Defined Metric)

The Create User-Defined Metric page allows you to specify the metric information required to successfully register the metric with Enterprise Manager. The page is divided into functional categories:

- Definition: You define the operational and environmental parameters required to run your script, in addition to the name of the script itself.

- Database Credentials: You enter the user name and password for a valid user account on the database where the SQL is to be run. Make sure the specified user account has the requisite administrative and access privileges to execute the SQL query or function call.

- Thresholds: To have the value returned by your SQL query or function call compared with set threshold values, enter the requisite threshold information. The value of the monitored metric returned by your query or function call will be compared against the thresholds you specify. If the comparison holds true, a warning or critical alert may be generated.

- Schedule: Specify the start time and frequency at which the user-defined SQL query or function call should be executed. The time zone used is that of the Agent running on the monitored machine.

The following figures show the Create User-Defined Metric pages for a SQL-based User-Defined Metric. When accessing this page from any Database home page, the Create User-Defined Metric page appears as shown in Figure 13-2.

Figure 13-2 Create User-Defined Metric Page (SQL-Based)

Description of "Figure 13-2 Create User-Defined Metric Page (SQL-Based)"

Key elements of this page are described in the following tables.

Table 13-6 Create User-Defined Metric Page: Definition

User-Interface Element | Description
--- | ---
Metric Name | Metric name identifying the User-Defined Metric in the Enterprise Manager user interface.
Metric Type | Type of the value returned by the user-defined script. Choose "NUMBER" if your script returns a number. Choose "STRING" if your script returns an alphanumeric text string.
SQL Query Output | Specify whether the SQL script is to return a single value (one column) or a multiple rows (two columns). Single Value: Query is one of the following types. A SELECT statement returning a single value. Example: SELECT sal FROM emp WHERE empno=7369 A function call returning a single value. Example: myfunc(123, 'abc') Two Columns: Query is a SELECT statement that returns two columns and possibly multiple rows. Example: SELECT ename, sal FROM emp. Each entry in the first column (the key column) must be a unique string. The second column (the value column) must be of the selected Metric Type.
SQL Query | Enter a SQL query or function call that returns values of the appropriate type (STRING or NUMBER). The SQL statement must return one or two column. If your SQL statement only returns one column, only one row can be returned. If you want multiple rows returned, your SQL statement must return two columns.

Table 13-7 Create User-Defined Metric Page: Database Credentials

User-Interface Element | Description
--- | ---
User Name | Enter the user name for a valid database account on the database where the SQL query is to be run. Make sure that the specified account has the requisite privileges to run the SQL query.
Password | Enter the password associated with the User Name.

Table 13-8 Create User-Defined Metric Page: Threshold

User-Interface Element | Description
--- | ---
Comparison Operator | Select the comparison method Enterprise Manager should use to compare the value returned by the SQL query or function call to the threshold values. When the query returns two columns, the second column (value column) will be used for comparison against threshold values. Available Comparison Operators Operator Value Metric Type Description = Number equal to > Number greater than < Number less than >= Number greater than or equal to <= Number less than or equal to != Number not equal to CONTAINS String contains at least MATCH String exact match
Warning | The value returned by the SQL query or function call is compared to the Warning threshold value using the specified comparison operator. If this comparison holds true, an alert triggers at the warning severity level. Specifically, an alert triggers at the warning severity level if the following comparison is true: <query_value> <comparison_operator> <warning_threshold> and if the consecutive occurrences preceding notification has been reached.
Critical | The value returned by the SQL query or function call is compared to the Critical threshold value using the specified comparison operator. If this comparison holds true, an alert triggers at the critical severity level. Specifically, an alert triggers at the critical severity level if the following comparison is true: <query_value> <comparison_operator> <critical_threshold> and if the consecutive occurrences preceding notification has been reached.
Warning Thresholds by Key and Critical Thresholds by Key | For queries returning two columns (the first column is the key and the second column is the value), you can specify thresholds on a per key basis. The following example uses the following query: SELECT ename FROM emp Threshold settings for this example are shown. Use the format key:value . Keys are case-sensitive. Warning:500 Critical:300 Comparison Operator: < Warning threshold by key: SMITH:250;JONES:400;CLARK:900 The warning threshold is set to 250 for SMITH, 400 for JONES, and 900 for CLARK. Critical threshold by key: SMITH:100;JONES:200;CLARK:500 The critical threshold is set to 100 for SMITH, 200 for JONES, and 500 for CLARK. All other keys will use the threshold values specified in the Warning and Critical fields.
Consecutive Occurrences Preceding Notification | Consecutive number of times a returned value reaches either the warning or critical thresholds before an alert triggers at a warning or critical severity. This feature is useful when monitoring for sustained conditions. For example, if your script monitors the CPU load for a particular machine, you do not want to be notified at every CPU load spike. Instead, you are only concerned if the CPU load remains at a particular threshold (warning or critical) level for a consecutive number of monitoring cycles.
Response Action | Optional. Specify a script or command that will be executed if the User-Defined Metric generates a warning or critical alert. The script or command will be executed using the credentials of the Agent owner. Important: Only an Enterprise Manager Super Administrator can create/edit response actions for metrics. For example, the Management Agent executes the response action if: The Alert severity is Warning or Critical AND There is a change in severity (for example, warning -> critical, critical --> warning, clear --> warning or critical) For more information, see Enterprise Manager online help.
Alert Message | Enter a custom message (up to 400 characters) to be used when an alert is sent. The default message uses %Key% and %value% variables to display the metric key and its returned value. The %Key% and %value% variables are case-sensitive. For example, a payroll system alert for underpayment of salary might be defined as: Underpaid Employee: %Key% has salary of %value% If the SQL query returns 2 columns, you can use the %Key% variable to represent the key value and the %value% variable to represent the return value. If the SQL query returns 1 column, only the %value% variable is applicable in the alert message.

The User-Defined Metric Schedule interface lets you specify the frequency at which the SQL query or function should be run.

### 13.3.1 SQL-Based User-Defined Metric Examples

For a database version 9i and higher, you can run the example queries as dbsnmp, which is the default monitoring user account for the Management Agent. On a 8.1.7 database (which does not have SELECT ANY DICTIONARY system privilege), you must grant dbsnmp the following privileges in order for the queries to run successfully:

For example #1:

    grant select on sys.dba_tablespaces to dbsnmp;
    grant select on sys.dba_data_files to dbsnmp;
    grant select on sys.dba_free_space to dbsnmp;

For example #2:

    grant select on sys.dba_extents to dbsnmp;

The above grant statements can be run as SYSDBA after logging in via "connect internal". The queries can also be run by any user who has been granted the DBA role.

#### 13.3.1.1 Example 1: Query Returning Tablespace Name and Percent Used

This sample User-Defined Metric monitors the percentage of space used for dictionary managed permanent tablespaces. A DBA can use this as a reference on when to add datafiles for the tablespace.Oracle recommends setting a polling frequency of 30 minutes, warning threshold at 75, and critical threshold at 85.

Example 1 SQL

    SELECT d.tablespace_name,
         round(((a.bytes - NVL(f.bytes,0))*100/a.maxbytes),2) used_pct
    FROM   sys.dba_tablespaces d,
          (select tablespace_name, sum(bytes) bytes, sum(greatest(maxbytes,bytes)) maxbytes
             from sys.dba_data_files group by tablespace_name) a,
          (select tablespace_name, sum(bytes) bytes
             from sys.dba_free_space group by tablespace_name) f
    WHERE d.tablespace_name = a.tablespace_name(+)
     AND d.tablespace_name = f.tablespace_name(+)
     AND NOT (d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY')

#### 13.3.1.2 Example 2: Query Returning Segment Name/Type and Extent Count

This sample User-Defined Metric checks for non-system table and index segments that are reaching a high number of extents. A high number of extents could indicate a segment with fragmentation and/or performance problems. A DBA can use this as a reference on when to call Segment Shrink or the Reorganization Wizard in Enterprise Manager.Oracle recommends setting a polling frequency of 24 hours, warning threshold at 1000, and critical threshold at 2000.

Example 2 SQL

    SELECT decode(nvl(partition_name, ' '),
                 ' ',   owner || '.' || segment_name || ' ' || segment_type,
                 owner || '.' || segment_name || '.' || partition_name || ' ' || segment_type) as segment,
          count(extent_id) as extent_count
    FROM dba_extents
    WHERE (segment_type like 'TABLE%' OR segment_type like 'INDEX%') AND
         (owner != 'SYSTEM' AND owner != 'SYS')
    GROUP BY owner, segment_name, partition_name, segment_type
    ORDER BY EXTENT_COUNT DESC

## 13.4 Notifications, Corrective Actions, and Monitoring Templates

User-Defined Metrics, because they are treated like other metrics, can take advantage of Enterprise Manager's notification system, corrective actions and monitoring templates.

Note: Corrective actions and monitoring templates support both OS USer-Defined Metrics and SQL-based User-Defined Metrics that return single scalar values.

### 13.4.1 Getting Notifications for User-Defined Metrics

As with regular metrics, you can receive e-mail notifications when User-Defined Metric critical or warning alert severities are reached. Assuming you have already defined your e-mail addresses and notification schedule, the remaining task is to set up a notification rule for the User-Defined Metric.

To set up notification rules:

1. Click Preferences.

2. From the vertical navigation bar, click Rules if you are a Super Administrator or My Rules if you are a regular Enterprise Manager administrator.

3. Click Create to define a new notification rule. The Create Notification Rule pages appear.

4. From the General page, enter the required rule definition information and choose Target Type Host for OS-based User-Defined Metrics or choose Database Instance for SQL-based User-Defined Metrics.

5. On the Metrics page, click Add. A list of available metrics appears. To view all metrics on simultaneously, choose Show All from the drop-down menu.

6. Select User-Defined Numeric Metric or User-Defined String Metric based on the type of value returned by your User-Defined Metric.

7. In the Objects column, choose whether you want to receive notification for all User-Defined Metrics (All Objects) or specific User-Defined Metrics (Select).

   When choosing the Select option, enter the name of the User-Defined Metric, or specify multiple User-Defined Metrics separated by commas. You can use the wildcard character (%) to match patterns for specific User-Defined Metrics.

   You can search for available User-Defined Metrics using the search function (flashlight icon). However, search results will only show User-Defined Metrics that have at least one collected data point. For metrics that have not yet collected at least one data point, as may be the case for a newly created User-Defined Metric, you must specify them in the Select text entry field.

8. Select the severity or corrective action state for which you would like to receive the notification and then click Continue.

9. If you want to receive e-mail for the specified User-Defined Metric, go to the Notification Rule and check the "Send me E-mail" option.

10. Click OK to create the new notification rule. If you made the notification rule public, other administrators can subscribe to the same rule.

### 13.4.2 Setting Corrective Actions for User-Defined Metrics

Corrective actions allow you to specify automated responses to alerts ensuring that routine responses to alerts are automatically executed. Corrective actions can be defined for both SQL and OS-based User-Defined Metrics.

To set up corrective actions:

1. From a target home page, click Metric and Policy Settings from Related Links.

2. Locate and edit the User-Defined Metric.

3. From the Edit Advanced Settings page, click Add under Corrective Actions for the Critical or Warning alert severity and define the corrective action. Corrective actions can be defined for one or both alert severities.

### 13.4.3 Deploying User-Defined Metrics across many targets using Monitoring Templates

Monitoring Templates simplify the task of standardizing monitoring settings across your enterprise by allowing you to specify the monitoring settings once and applying them to your monitored targets. You can thus use Monitoring Templates as a way to propagate User-Defined Metrics across a large number of targets.

Assuming you have created the User-Defined Metric on the host or database target, you can use Monitoring Templates to propagate the User-Defined Metric to other hosts or database targets.

To create a Monitoring Template for the User-Defined Metric:

1. Click Setup.

2. From the vertical navigation bar, click Monitoring Templates

3. Click Create. The Copy Target Settings page appears.

4. Specify the host or database on which you defined the User-Defined Metric and click Continue.

5. Fill in the requisite information on the General page.

6. On the Metric Thresholds page, you can choose to keep or remove the other metrics that have been copied over from the target.

7. You can also edit the User-Defined Metric's thresholds, collection schedule, and corrective actions.

8. On the Policies page, you can choose to keep or remove any policy rules that have been copied over from the target.

9. Click OK to save the template settings to the Management Repository.

Once the template containing the User-Defined Metric has been created, you can propagate the User-Defined Metric by applying the template to other hosts or databases.

Important: For OS-based User-Defined Metrics, you will first need to separately deploy the OS Script used by the User-Defined Metric to all destination hosts. The OS Script should reside in the same location across all host targets.

To apply the monitoring template:

1. On the Monitoring Templates page, select the monitoring template and click Apply.

2. On the Apply Monitoring Template page, add the targets on which the User-Defined Metric should be created.

3. Under the section "Metrics with multiple thresholds", choose the option 'Duplicate threshold settings on target'.

   Important: if there are other metrics in the monitoring template, refer first to the Enterprise Manager online help for implications of what this option means for these other metrics.

4. Click Continue.

5. On the subsequent page, specify the credentials that should be used when running the User-Defined Metric on the destination targets.

6. Click Finish.

7. When you return back to the Monitoring Templates page, check that "Pending Apply Operations" count for your template is zero. This indicates the number of template apply operations that could be pending. Once they are all complete, the count should be zero.